package fy.hr.staffRedeploy;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import fy.config.FyConfig;
import fy.hr.bean.JobBean;
import fy.util.DBUtil;


final class StaffingNeedsDao {
	private static Log log = LogFactory.getLog(StaffingNeedsDao.class);

	public static List<JobBean> queryPostAndRank(String memberCode) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		List<JobBean> jobBeans = null;
		try {
			conn = DBUtil.connect(FyConfig.getEHR_DB_CONFIG());

			// 岗位
			pst = conn
					.prepareStatement("SELECT j.jobname || '(' || dept.deptname || ')', j.jobcode, d.docname, NVL(w1.workout, 0), NVL(w1.actualnum, 0) FROM om_job j LEFT JOIN ( SELECT  w.PK_OM_JOB ,  SUM(w.workout) AS workout,  SUM(w.actualnum) AS actualnum FROM  om_job_workout w WHERE  w.pk_corp || w.cyear || w.version IN (  SELECT pk_corp || cyear || version  FROM  om_corp_workout  WHERE PK_OM_CORP_WORKOUT IN (SELECT max(PK_OM_CORP_WORKOUT) FROM   om_corp_workout  WHERE    stat = '1' GROUP BY pk_corp)) GROUP BY w.PK_OM_JOB) w1 ON w1.PK_OM_JOB = j.PK_OM_JOB LEFT JOIN bd_defdoc d ON j.jobrank = d.pk_defdoc LEFT JOIN bd_deptdoc dept ON dept.pk_deptdoc = j.pk_deptdoc WHERE j.ISABORT = 'N' AND j.dr = 0 AND (j.pk_corp IN ( SELECT  p.pk_corp FROM  bd_psndoc p WHERE  p.psncode = ?  AND p.psnclscope = 0  AND NVL(p.dr, 0) = 0))ORDER BY dept.deptcode,j.jobcode ASC");

			pst.setString(1, memberCode);

			rs = pst.executeQuery();

			while (rs.next()) {
				JobBean bean = new JobBean();
				bean.setName(rs.getString(1));
				bean.setId(rs.getString(2));
				bean.setRank(rs.getString(3));
				bean.setWorkout(rs.getString(4));
				bean.setActualnum(rs.getString(5));

				if (jobBeans == null) {
					jobBeans = new ArrayList<JobBean>();
				}
				jobBeans.add(bean);
			}

		} catch (Exception e) {
			StringBuilder sb = new StringBuilder().append("查询ＨＲ部门对应的岗位和职级失败");
			log.error(sb, e);
			System.out.println(sb);
			e.printStackTrace();

		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}
		return jobBeans;
	}
}
